﻿using System;
using System.Data.OleDb;
using System.Data;
using BarCode.Common;

namespace BarCode.Module
{
    public class AccessHelper
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(AccessHelper));

        private string conn_str = null;
        private OleDbConnection ole_connection = null;
        private OleDbCommand ole_command = null;
        private OleDbDataReader ole_reader = null;
        private DataTable dt = null;

        private void InitDB()
        {
            ole_connection = new OleDbConnection(conn_str);//创建实例
            ole_command = new OleDbCommand();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db_path">数据库路径</param>
        public AccessHelper(string db_path)
        {
            conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + db_path + "'";
            InitDB();
        }

        public void Release()
        {
            if (ole_connection != null)
            {
                if (ole_connection.State != ConnectionState.Closed)
                {
                    ole_connection.Close();
                }
                ole_connection.Dispose();
            }

            if (ole_command != null)
            {
                ole_command.Dispose();
            }
        }


        /// <summary>
        /// 转换数据格式
        /// </summary>
        /// <param name="reader">数据源</param>
        /// <returns>数据列表</returns>
        private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)
        {
            DataTable dt_tmp = null;
            DataRow dr = null;
            int data_column_count = 0;
            int i = 0;

            data_column_count = reader.FieldCount;
            dt_tmp = BuildAndInitDataTable(data_column_count);

            if (dt_tmp == null)
            {
                return null;
            }

            while (reader.Read())
            {
                dr = dt_tmp.NewRow();

                for (i = 0; i < data_column_count; ++i)
                {
                    dr[i] = reader[i];
                }

                dt_tmp.Rows.Add(dr);
            }

            return dt_tmp;
        }

        /// <summary>
        /// 创建并初始化数据列表
        /// </summary>
        /// <param name="Field_Count">列的个数</param>
        /// <returns>数据列表</returns>
        private DataTable BuildAndInitDataTable(int Field_Count)
        {
            DataTable dt_tmp = null;
            DataColumn dc = null;
            int i = 0;

            if (Field_Count <= 0)
            {
                return null;
            }

            dt_tmp = new DataTable();

            for (i = 0; i < Field_Count; ++i)
            {
                dc = new DataColumn(i.ToString());
                dt_tmp.Columns.Add(dc);
            }

            return dt_tmp;
        }

        /// <summary>
        /// 从数据库里面获取数据
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>数据列表</returns>
        public DataTable GetDataTableFromDB(string strSql)
        {
            if (conn_str == null)
            {
                return null;
            }

            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }

                if (ole_connection.State == ConnectionState.Closed)
                {
                    return null;
                }

                ole_command.CommandText = strSql;
                ole_command.Connection = ole_connection;

                ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);

                dt = ConvertOleDbReaderToDataTable(ref ole_reader);

                ole_reader.Close();
                ole_reader.Dispose();
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            return dt;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回结果</returns>
        public int ExecuteSql(string strSql)
        {
            int nResult = 0;

            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return nResult;
                }

                ole_command.Connection = ole_connection;
                ole_command.CommandText = strSql;

                nResult = ole_command.ExecuteNonQuery();
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.ToString());
                return nResult;
            }
            return nResult;
        }


        /// <summary>    
        /// 执行SQL语句，返回影响的记录数    
        /// </summary>    
        /// <param name="SQLString">SQL语句</param>    
        /// <returns>影响的记录数</returns>    
        public int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
        {
            int nResult = 0;
            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return nResult;
                }
                PrepareCommand(ole_command, ole_connection, null, SQLString, cmdParms);
                nResult = ole_command.ExecuteNonQuery();
                ole_command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
            }
            return nResult;

        }

        public int ExecuteNonQuery(string SQLString, params OleDbParameter[] cmdParms)
        {
            int nResult = 0;
            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }
                
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return nResult;
                }
                PrepareCommand(ole_command, ole_connection, null, SQLString, cmdParms);
                nResult = ole_command.ExecuteNonQuery();
                ole_command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
            }
            return nResult;

        }



        /// <summary>    
        /// 执行查询语句，返回DataSet    
        /// </summary>    
        /// <param name="SQLString">查询语句</param>    
        /// <returns>DataSet</returns>    
        public DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
        {
            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return null;
                }
                PrepareCommand(ole_command, ole_connection, null, SQLString, cmdParms);
                using (OleDbDataAdapter da = new OleDbDataAdapter(ole_command))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        ole_command.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                return null;
            }
        }

        /// <summary>    
        /// 执行查询语句，返回DataSet    
        /// </summary>    
        /// <param name="SQLString">查询语句</param>    
        /// <returns>DataSet</returns>    
        public object ExecuteScalar(string SQLString, params OleDbParameter[] cmdParms)
        {
            try
            {
                ole_connection.Open();//打开数据库连接
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return null;
                }
                PrepareCommand(ole_command, ole_connection, null, SQLString, cmdParms);
                object retObj = ole_command.ExecuteScalar();
                ole_command.Parameters.Clear();
                return retObj;
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                return null;
            }
        }

        /// <summary>    
        /// 执行查询语句，返回DataSet    
        /// </summary>    
        /// <param name="SQLString">查询语句</param>    
        /// <returns>DataSet</returns>    
        public DataSet Query(string SQLString)
        {
            try
            {
                if (ole_connection.State != ConnectionState.Open)
                {
                    ole_connection.Open();//打开数据库连接
                }
                if (ole_connection.State == ConnectionState.Closed)
                {
                    return null;
                }
                using (OleDbDataAdapter da = new OleDbDataAdapter(SQLString, ole_connection))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                return null;
            }
        }

        private void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;    
            if (cmdParms != null)
            {


                foreach (OleDbParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}
